package ${groupId}.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import cn.hutool.core.util.StrUtil;

public final class DBUtil {

	public String artifactId = "${artifactId}";
	private String user = "root";
	private String password = "142857";
	private String schema = artifactId;
	private String url = "jdbc:mysql://localhost:3306/";
	private String param = "?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private String table_sql = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME NOT IN ('role_info','menu_info','user_info')";
	private String field_sql = "SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_COMMENT,COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?";

	public Connection getCon() {
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url+schema+param, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	
	public List<TableAndFields> create() throws Exception {
		List<TableAndFields> list = new ArrayList<TableAndFields>();
		Connection con = this.getCon();
		PreparedStatement tablePS = con.prepareStatement(table_sql);
		tablePS.setObject(1, schema);
		//获取当前数据库下所有表的信息
		ResultSet tableRS = tablePS.executeQuery();
		while (tableRS.next()) {
			TableAndFields taf = new TableAndFields();
			TableInfo tableInfo = new TableInfo();
			tableInfo.imports = new HashSet<String>();
			//数据库表名
			String tableName = tableRS.getString("TABLE_NAME");
			tableInfo.tableName = tableName;
			//java类名
			String className = StringUtil.tableNameConvert(tableName);
			tableInfo.className = className;
			tableInfo.leftJoinTableName = "";
			//数据库表注释
        	String tableComment = tableRS.getString("TABLE_COMMENT");
        	tableInfo.notes = tableComment;
        	PreparedStatement fieldPS = con.prepareStatement(field_sql);
        	fieldPS.setObject(1, tableName);
        	fieldPS.setObject(2, schema);
        	//获取数据库表的所有字段信息
    		ResultSet fieldRS = fieldPS.executeQuery();
    		List<FieldInfo> lf = new ArrayList<FieldInfo>();
    		while (fieldRS.next()) {
    			FieldInfo fieldInfo = new FieldInfo();
    			//表字段名
    			fieldInfo.dbName = fieldRS.getString("COLUMN_NAME");
    			if ("primary_key".equals(fieldInfo.dbName)) {
					continue;
				}
    			//java属性名
    			fieldInfo.javaName = StringUtil.fieldNameConvert(fieldInfo.dbName);
    			fieldInfo.getMethod = StringUtil.getMethod(fieldInfo.javaName);
    			fieldInfo.setMethod = StringUtil.setMethod(fieldInfo.javaName);
    			//表字段注释
            	fieldInfo.notes = fieldRS.getString("COLUMN_COMMENT");
            	//表字段类型
            	fieldInfo.dbType = fieldRS.getString("DATA_TYPE");
            	//表字段长度
            	fieldInfo.length = fieldRS.getString("CHARACTER_MAXIMUM_LENGTH");
            	fieldInfo.foreignKeyTableName = "";
            	fieldInfo.jsRegular = "required";
            	fieldInfo.textField = false;
            	fieldInfo.orderType = false;
            	fieldInfo.compositeKeys = false;
            	fieldInfo.htmlFormType = 1;
            	switch (fieldInfo.dbType) {
				case "varchar":
					fieldInfo.javaType = "String";
					varcharSwitch(fieldInfo,tableInfo);
					break;
				case "int":
					fieldInfo.javaType = "Integer";
					fieldInfo.length = StrUtil.sub(fieldRS.getString("COLUMN_TYPE"), 4, fieldRS.getString("COLUMN_TYPE").length()-1);
					intSwitch(fieldInfo,tableInfo);
					break;
				case "double":
					fieldInfo.javaType = "Double";
					fieldInfo.length = StrUtil.sub(fieldRS.getString("COLUMN_TYPE"), 4, fieldRS.getString("COLUMN_TYPE").length()-1);
					intSwitch(fieldInfo,tableInfo);
					break;
				case "datetime":
					fieldInfo.javaType = "Date";
					fieldInfo.length = "yyyy-MM-dd hh:mm:ss";
					fieldInfo.htmlFormType = 2;
					tableInfo.imports.add("java.util.Date");
					tableInfo.imports.add("org.springframework.format.annotation.DateTimeFormat");
					break;
				}
            	lf.add(fieldInfo);
			}
    		int columnCount = lf.size();
    		//获取数据库表的列数
        	if (columnCount == 2) {
				//2列为数据字典表
        		tableInfo.tableType = 1;
			} else {
				//大于2列为业务表
				tableInfo.tableType = 2;
			}
        	taf.tableInfo = tableInfo;
    		taf.fieldInfos = lf;
    		list.add(taf);
		}
		return list;
	}
	
	public FieldInfo varcharSwitch(FieldInfo fieldInfo,TableInfo tableInfo) {
		switch (fieldInfo.length) {
		case "50":
			fieldInfo.foreignKeyTableName = fieldInfo.dbName;
			tableInfo.leftJoinTableName = fieldInfo.dbName;
			break;
		case "51":
			fieldInfo.compositeKeys = true;
			break;
		case "52":
			fieldInfo.textField = true;
			break;
		case "60":
			fieldInfo.listParam = 1;
			break;
		case "61":
			fieldInfo.listParam = 2;
			break;
		}
		return fieldInfo;
	}
	
	public FieldInfo intSwitch(FieldInfo fieldInfo,TableInfo tableInfo) {
		switch (fieldInfo.length) {
		case "1":
			break;
		case "2":
			break;
		}
		return fieldInfo;
	}
	
}
